package id.co.shuba.spamobile.data;

import java.text.ParseException;
import java.util.Date;

import id.co.shuba.spamobile.model.DSARInfo;
import id.co.shuba.spamobile.model.ProspectInfo;
import id.co.shuba.spamobile.model.StandardInfo;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DSARDB extends BaseDB {	
	
	public DSARDB(DatabaseHandler db) {
		super(db);
	}
	
	public Cursor getList(String prospectID) {
		String sql = "SELECT D.VISIT_NO _id, D.DATE_VISIT_PLAN,"
				+ "O.STD_TEXT AS OBJECTIVE_OF_VISIT  FROM TBL_PROSPECT_VISIT D "
				+ "LEFT JOIN TBL_MS_STANDARD O ON O.STD_GROUP = ? "
				+ "AND O.STD_VALUE = D.OBJECTIVE_OF_VISIT WHERE IS_DELETED = '0' AND D.PROSPECT_ID = ?";

		String[] params = new String[] {
				String.valueOf(StandardDB.GROUP_OBJECTIVE_OF_VISIT),
				String.valueOf(prospectID) };

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, params);

		return cursor;
	}
	
	public Cursor getList() {
		String sql = "SELECT * FROM TBL_PROSPECT_VISIT WHERE IS_DELETED = '0'";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}
	
	public Cursor getListExpot(String aoCode) {
		String sql = "SELECT PV.* FROM TBL_PROSPECT_VISIT PV INNER JOIN TBL_PROSPECT P ON PV.PROSPECT_ID = P.PROSPECT_ID WHERE P.AO_CODE = '"+aoCode+"'";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}
	
	public boolean hasOutstandingVisit(String prospectID) {
		String sql = "SELECT VISIT_NO FROM TBL_PROSPECT_VISIT WHERE IS_DELETED = '0' AND PROSPECT_ID = ? AND (OBJECTIVE_OF_VISIT IS NULL OR OBJECTIVE_OF_VISIT = '')";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] {prospectID});			
							
		boolean exists = cursor.getCount() > 0;
		
		cursor.close();
		return exists;
	}	
	
	public Cursor getListUpload(String aoCode) {
		String sql = "SELECT PV.* FROM TBL_PROSPECT_VISIT PV INNER JOIN TBL_PROSPECT P ON PV.PROSPECT_ID = P.PROSPECT_ID WHERE P.AO_CODE = '"+aoCode+"' AND PV.IS_UPLOAD = '0'";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}	
	
	public int generateVisitNo(String prospectId) {
		SQLiteDatabase db = _db.getReadableDatabase();
		return generateVisitNo(db, prospectId);
	}
	
	public static int generateVisitNo(SQLiteDatabase db, String prospectId) {
		String sql = "SELECT IFNULL(MAX(VISIT_NO),0) AS VISIT_NO FROM TBL_PROSPECT_VISIT WHERE PROSPECT_ID=@PROSPECT_ID";		
		Cursor cursor = db.rawQuery(sql, new String[] { prospectId });
		
		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return 0;
		}
		
		int result = cursor.getInt(cursor.getColumnIndex("VISIT_NO"));		
		cursor.close();

		return result;
	}
	
	private static ContentValues createContentValues(DSARInfo info) {
		ContentValues values = new ContentValues();
		values.put("VISIT_NO", info.getVisitNo());
		values.put("PROSPECT_ID", info.getProspectID());
		values.put("DATE_VISIT_PLAN", info.getFullVisitPlan());
		values.put("DATE_VISIT_ACTUAL", info.getFullVisitActual());
		values.put("JOIN_FIELD_WORK", info.getJoinFieldWork());
		values.put("OBJECTIVE_OF_VISIT", info.getObjectiveOfVisit());
		values.put("RESULT_OF_VISIT", info.getResultOfVisit());
		values.put("REMARK", info.getRemark());		
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		return values;
	}
	
	public static void add(SQLiteDatabase db, DSARInfo info) {
		ContentValues values = createContentValues(info);
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		db.insert("TBL_PROSPECT_VISIT", null, values);
		
	}
	
	public static void addOrUpdate(SQLiteDatabase db, DSARInfo info) throws ParseException {
		ContentValues values = createContentValues(info);				
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());	
		values.put("IS_DELETED", info.getIsDeleted());
		values.put("UPLOAD_BY", info.getUploadBy());
		values.put("UPLOAD_DATE", info.getUploadDate());
		values.put("DATE_VISIT_PLAN", info.getDateVisitPlan());
		values.put("DATE_VISIT_ACTUAL", info.getDateVisitActual());
		
		Cursor cursor = db.rawQuery("SELECT UPDATE_DATE FROM TBL_PROSPECT_VISIT WHERE PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] {info.getProspectID(), String.valueOf(info.getVisitNo())});
						
		Date upd = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			upd = cursorGetDate(cursor, "UPDATE_DATE");			
		}
		cursor.close();						
		
		if (upd == null) {			
			db.insert("TBL_PROSPECT_VISIT", null, values);
		} else if ( dateFormat.parse(info.getUpdateDate()).getTime() > upd.getTime()) {
			db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ? AND VISIT_NO = ?",
					new String[] { info.getProspectID(),  String.valueOf(info.getVisitNo())});
		}
	}	
	
	public static int updateUpload(SQLiteDatabase db, String prospectId, int visitNo, String uploadBy) throws ParseException {
		ContentValues values = new ContentValues();
		values.put("IS_UPLOAD", "1");
		values.put("UPLOAD_DATE", getCurrentDateTime());
		values.put("UPLOAD_BY", uploadBy);
		
		return db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] { prospectId, String.valueOf(visitNo)});
	}
	
	public void add(DSARInfo info) {
		ContentValues values = new ContentValues();
		values.put("VISIT_NO", info.getVisitNo());
		values.put("PROSPECT_ID", info.getProspectID());
		values.put("DATE_VISIT_PLAN", info.getFullVisitPlan());		
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("IS_UPLOAD", "0");
		values.put("IS_DELETED", "0");
		
		SQLiteDatabase db = _db.getWritableDatabase();

		db.insert("TBL_PROSPECT_VISIT", null, values);
	}		

	public void update(DSARInfo info) {
		ContentValues values = new ContentValues();		
		values.put("DATE_VISIT_ACTUAL", info.getFullVisitActual());
		values.put("JOIN_FIELD_WORK", info.getJoinFieldWork());
		values.put("OBJECTIVE_OF_VISIT", info.getObjectiveOfVisit());
		values.put("RESULT_OF_VISIT", info.getResultOfVisit());
		values.put("REMARK", info.getRemark());		
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());

		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] { info.getProspectID(), String.valueOf(info.getVisitNo()) });
	}
	
	public void updateDateVisitPlan(DSARInfo info) {
		ContentValues values = new ContentValues();		
		values.put("DATE_VISIT_PLAN", info.getFullVisitPlan());			
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());

		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] { info.getProspectID(), String.valueOf(info.getVisitNo()) });
	}
	
	public void delete(String prospectId, int visitNo, String user) {		
		SQLiteDatabase db = _db.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("IS_DELETED", "1");
		values.put("IS_UPLOAD", "0");
		values.put("UPDATE_DATE", getCurrentDateTime());
		values.put("UPDATE_BY", user);
		
		db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] { prospectId, String.valueOf(visitNo) });
	}
	
	public DSARInfo get(String prospectId, int visitNo) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM TBL_PROSPECT_VISIT WHERE PROSPECT_ID = ? AND VISIT_NO = ?",
				new String[] { prospectId, String.valueOf(visitNo) });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		DSARInfo info = new DSARInfo();
		info.setProspectID(prospectId);
		info.setVisitNo(visitNo);
		info.setVisitPlan(cursorGetString(cursor, "DATE_VISIT_PLAN"));
		info.setVisitActual(cursorGetString(cursor, "DATE_VISIT_ACTUAL"));
		info.setJoinFieldWork(cursorGetString(cursor, "JOIN_FIELD_WORK"));
		info.setObjectiveOfVisit(cursorGetString(cursor, "OBJECTIVE_OF_VISIT"));
		info.setResultOfVisit(cursorGetString(cursor, "RESULT_OF_VISIT"));
		info.setRemark(cursorGetString(cursor, "REMARK"));
		info.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		cursor.close();

		return info;
	}
	
	public DSARInfo get(String prospectId) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM TBL_PROSPECT_VISIT WHERE PROSPECT_ID = ? AND IS_DELETED = '0' ORDER BY VISIT_NO LIMIT 1",
				new String[] { prospectId });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		DSARInfo info = new DSARInfo();
		info.setProspectID(prospectId);
		info.setVisitNo(cursor.getInt(cursor.getColumnIndex("VISIT_NO")));
		info.setVisitPlan(cursorGetString(cursor, "DATE_VISIT_PLAN"));
		info.setVisitActual(cursorGetString(cursor, "DATE_VISIT_ACTUAL"));
		info.setJoinFieldWork(cursorGetString(cursor, "JOIN_FIELD_WORK"));
		info.setObjectiveOfVisit(cursorGetString(cursor, "OBJECTIVE_OF_VISIT"));
		info.setResultOfVisit(cursorGetString(cursor, "RESULT_OF_VISIT"));
		info.setRemark(cursorGetString(cursor, "REMARK"));
		info.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		cursor.close();

		return info;
	}
}
